package com.game.server.db;

import com.game.majiang.config.LogManager;
import com.sun.rowset.CachedRowSetImpl;

import javax.sql.rowset.CachedRowSet;
import java.io.PrintWriter;
import java.io.StringReader;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Arrays;

/**
 * SQL 基本操作 通过它,可以很轻松的使用 JDBC 来操纵数据库
 * 
 * @author 
 */
public class SqlHelperwrite {
    private static org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(SqlHelperwrite.class);
    private static SqlHelperwrite _instance;

    public static synchronized SqlHelperwrite getInstance() {
        if (_instance == null) {
            _instance = new SqlHelperwrite();
        }
        return _instance;
    }

    /**
     * 不允许实例化该类
     */
    private SqlHelperwrite() {
    }

    /**
     * 获取一个带参数的 PreparedStatement 该 PreparedStatement 已经设置数据集 可以滚动,可以更新
     * 
     * @param cmdText
     *            需要 ? 参数的 SQL 语句
     * @param cmdParams
     *            SQL 语句的参数表
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     */
    private static CallableStatement getCallableStatement(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        Connection conn = null;
        CallableStatement cstmt = null;
        try {
            conn = DBConnectionPool.getInstance().getConnection(100);
        } catch (Exception ex) {

            ex.printStackTrace();
            log.error(ex);
            StringWriter errors = new StringWriter();
            ex.printStackTrace(new PrintWriter(errors));
            log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    .......");
        }
        if (conn != null) {
            try {
                cstmt = conn.prepareCall(cmdText, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                int i = 1;
                for (Parameter item : cmdParams) {
                    if (item.getType() == ParameterType.Input) {
                        setParaemter(cstmt, i, item);
                    }
                    if (item.getType() == ParameterType.OutPut) {
                        cstmt.registerOutParameter(i, item.getSqlType());
                    }
                    if (item.getType() == ParameterType.InOut) {
                        setParaemter(cstmt, i, item);
                        cstmt.registerOutParameter(i, item.getSqlType());
                    }
                    i++;
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("冰冻获取数据库链接出错");
                log.error(e);
                StringWriter errors = new StringWriter();
                e.printStackTrace(new PrintWriter(errors));
                log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    .......");
                close(conn, false);
            }
        }
        return cstmt;
    }

    private static void setParaemter(CallableStatement cstmt, int i, Parameter item) throws SQLException {
        if (item.getSqlType() == Types.INTEGER) {
        	if(item.getValue() == null)
        	{
        		cstmt.setNull(i,  Types.INTEGER);
        	}
        	else
        	{
        		cstmt.setInt(i, (Integer) item.getValue());
        	}
        }
        if (item.getSqlType() == Types.VARCHAR) {
            cstmt.setCharacterStream(i, item.getValue() == null ? new StringReader("") : new StringReader((String) item.getValue()));
        }
        if (item.getSqlType() == Types.NVARCHAR) {
            cstmt.setNCharacterStream(i, item.getValue() == null ? new StringReader("") : new StringReader((String) item.getValue()));
        }
        if (item.getSqlType() == Types.BIT) {
            cstmt.setBoolean(i, (Boolean) item.getValue());
        }
        if (item.getSqlType() == Types.DATE) {
            cstmt.setDate(i, (Date) item.getValue());
        }
        if (item.getSqlType() == Types.TIME) {
            cstmt.setTime(i, (Time) item.getValue());
        }
        if (item.getSqlType() == Types.TIMESTAMP) {
            cstmt.setTimestamp(i, (Timestamp) item.getValue());
        }
        if (item.getSqlType() == Types.BIGINT) {
            cstmt.setLong(i, (Long) item.getValue());
        }
        if (item.getSqlType() == Types.TINYINT) {
        	if(item.getValue() == null)
        	{
        		cstmt.setNull(i,Types.TINYINT);
        	}
        	else
        	{
        		cstmt.setInt(i,(Byte)item.getValue());
        	}
        }
        if (item.getSqlType() == Types.SMALLINT) {
            cstmt.setShort(i, (Short) item.getValue());
        }
        if (item.getSqlType() == Types.FLOAT) {
            cstmt.setFloat(i, (Float) item.getValue());
        }
        if (item.getSqlType() == Types.BOOLEAN) {
            cstmt.setBoolean(i, (Boolean) item.getValue());
        }
        if (item.getSqlType() == Types.DECIMAL) {
            cstmt.setBigDecimal(i, (BigDecimal) item.getValue());
        }
    }

    /**
     * 获取一个 Statement 该 Statement 已经设置数据集 可以滚动,可以更新
     * 
     * @return 如果获取失败将返回 null,调用时记得检查返回值
     */
    private static Statement getStatement() {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DBConnectionPool.getInstance().getConnection(100);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (conn != null) {
            try {
                stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            } catch (SQLException ex) {
                close(conn, false);
            }
        }
        return stmt;
    }

    /**
     * 返回一个 ResultSet
     * 
     * @param cmdText
     *            需要 ? 参数的 SQL 语句
     * @param cmdParams
     *            SQL 语句的参数表
     * @return
     */
    public static CachedRowSet getResultSet(String cmdText, Parameter... cmdParams) {

        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));

        long l = System.currentTimeMillis();
        CachedRowSet rs = null;
        if (cmdParams.length == 0)
    	{
        	try {
        		rs = getResultSetByStatement(cmdText);
                LogManager.getInstance().Log("返回一个 ResultSet"+rs);
        		//LogManager.getInstance().Logsym(cmdText);
			} catch (Exception e) {
				log.error(e);
				StringWriter errors = new StringWriter();  
				e.printStackTrace(new PrintWriter(errors));  
				log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    ......."); 
			}
    	}
        else
        {
        	try {
        		rs = getResultSetByCallableStatement(cmdText, cmdParams);
			} catch (Exception e) {
				log.error(e);
				StringWriter errors = new StringWriter();  
				e.printStackTrace(new PrintWriter(errors));  
				log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    ......."); 
			}
        }
        //Logger.getLogger(SqlHelper.class.getName()).log(Level.INFO, "SQL " + cmdText + "消耗" + (System.currentTimeMillis() - l) + "毫秒\n");
        return rs;
    }

    /**
     * 返回结果集的第一行的一列的值,其他忽略
     * 
     * @param cmdText
     *            SQL 语句
     * @return
     */
    public static Object execScalar(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        ResultSet rs = null;
        try {
            rs = getResultSet(cmdText, cmdParams);
		} catch (Exception e) {
			log.error(e);
		}
        Object obj = null;
        try {
            obj = buildScalar(rs);
		} catch (Exception e) {
			log.error(e);
		}
        return obj;
    }

    /**
     * 返回一个 ResultSet
     * 
     * @param cmdText
     *            cmdText SQL语句
     * @return
     */
    private static CachedRowSet getResultSetByStatement(String cmdText) {
        log.info(cmdText);
        Statement stmt = getStatement();
        ResultSet rs = null;
        CachedRowSet crs = null;
        if (stmt != null) {
            boolean flag = true;
            try {
                crs = new CachedRowSetImpl();
                rs = stmt.executeQuery(cmdText);
                crs.populate(rs);
            } catch (SQLException ex) {
                flag = false;
                log.error(ex);
    			StringWriter errors = new StringWriter();  
    			ex.printStackTrace(new PrintWriter(errors));  
    			log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    ......."); 
            } finally {
                close(stmt, flag);
            }
        }
        return crs;
    }

    /**
     * 返回一个 ResultSet
     * 
     * @param cmdText
     *            需要 ? 参数的 SQL 语句
     * @param cmdParams
     *            SQL 语句的参数表
     * @return
     */
    private static CachedRowSet getResultSetByCallableStatement(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        CallableStatement cstmt = getCallableStatement(cmdText, cmdParams);
        ResultSet rs = null;
        CachedRowSet crs = null;
        if (cstmt != null) {
            boolean flag = true;
            try {
                crs = new CachedRowSetImpl();
                rs = cstmt.executeQuery();
                crs.populate(rs);
                int i = 1;
                for (Parameter item : cmdParams) {
                    if (item.getType() == ParameterType.OutPut || item.getType() == ParameterType.InOut) {
                        item.setValue(cstmt.getObject(i));
                    }
                    i++;
                }
               cstmt.cancel();
            } catch (SQLException ex) {
                flag = false;
                log.error(ex);
    			StringWriter errors = new StringWriter();  
    			ex.printStackTrace(new PrintWriter(errors));  
    			log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    ......."); 
            } finally {
                close(cstmt, flag);
            }
        }
        return crs;
    }
    
    public static int executerekey(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        long l = System.currentTimeMillis();
        int id=0;
        if (cmdParams.length == 0)
    	{
        	try {
        		id = execSqlrekey(cmdText);				
			} catch (Exception e) {
				log.error(e);
				StringWriter errors = new StringWriter();  
				e.printStackTrace(new PrintWriter(errors));  
				log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    ......."); 
			}
    	}
        else
    	{
        	try {
        		id = execProcrekey(cmdText, cmdParams);
			} catch (Exception e) {
				log.error(e);
				StringWriter errors = new StringWriter();  
				e.printStackTrace(new PrintWriter(errors));  
				log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    ......."); 
			}
    	}
        //Logger.getLogger(SqlHelperwrite.class.getName()).log(Level.INFO, "SQL " + cmdText + "消耗" + (System.currentTimeMillis() - l) + "毫秒\n");
        return id;
    }

    public static int execute(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        long l = System.currentTimeMillis();
        int updateCount=0;
        if (cmdParams.length == 0)
    	{
        	try {
        		updateCount = execSql(cmdText);
			} catch (Exception e) {
				log.error(e);
				StringWriter errors = new StringWriter();  
				e.printStackTrace(new PrintWriter(errors));  
				log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    ......."); 
				try {
	        		updateCount = execSql(cmdText);
				} catch (Exception e2) {
					log.error(e2);
					StringWriter errors2 = new StringWriter();  
					e2.printStackTrace(new PrintWriter(errors2));  
					log.error("第二次失败 exception:"+e2.getMessage()+"\t"+errors2.toString()+"    ......."); 
				}
			}
    	}
        else
        {
        	try {
                updateCount = execProc(cmdText, cmdParams);
			} catch (Exception e) {
				log.error(e);
				StringWriter errors = new StringWriter();  
				e.printStackTrace(new PrintWriter(errors));  
				log.error(" exception:"+e.getMessage()+"\t"+errors.toString()+"    ......."); 
				try {
	                updateCount = execProc(cmdText, cmdParams);
				} catch (Exception e2) {
					log.error(e2);
					StringWriter errors2 = new StringWriter();  
					e2.printStackTrace(new PrintWriter(errors2));  
					log.error("第二次失败 exception:"+e2.getMessage()+"\t"+errors2.toString()+"    ......."); 
				}
			}
        }
        long distime = System.currentTimeMillis() - l;
        if(distime > 500)
        {
            LogManager.getInstance().Error("zhuSQL " + cmdText + "消耗" + distime + "毫秒\n");
        }
        return updateCount;
    }

    private static int execProc(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        CallableStatement cstmt = getCallableStatement(cmdText, cmdParams);
        int updateCount = -1;
        if (cstmt != null) {
            boolean flag = true;
            try {
                updateCount = cstmt.executeUpdate();
                int i = 1;
                for (Parameter item : cmdParams) {
                    if (item.getType() == ParameterType.OutPut || item.getType() == ParameterType.InOut) {
                        item.setValue(cstmt.getObject(i));
                    }
                    i++;
                }
                cstmt.cancel();
            } catch (SQLException ex) {
                flag = false;
                log.error(ex);
    			StringWriter errors = new StringWriter();  
    			ex.printStackTrace(new PrintWriter(errors));  
    			log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    ......."); 
            } finally {
                close(cstmt, flag);
            }
        }
        return updateCount;
    }
    
    private static int execProcrekey(String cmdText, Parameter... cmdParams) {
        log.info(cmdText);
        log.info(Arrays.toString(cmdParams));
        CallableStatement cstmt = getCallableStatement(cmdText, cmdParams);
        int id = -1;
        if (cstmt != null) {
            boolean flag = true;
            try {
                cstmt.executeUpdate();
                int i = 1;
                for (Parameter item : cmdParams) {
                    if (item.getType() == ParameterType.OutPut || item.getType() == ParameterType.InOut) {
                        item.setValue(cstmt.getObject(i));
                    }
                    i++;
                }
                ResultSet rs = cstmt.getGeneratedKeys();
                if(rs.next()) { 
                	id = rs.getInt(1);
                }      
                cstmt.cancel();
            } catch (SQLException ex) {
                flag = false;
                log.error(ex);

            } finally {
                close(cstmt, flag);
            }
        }
        return id;
    }
    
    private static int execSqlrekey(String cmdText) {
        log.info(cmdText);
        Statement stmt = getStatement();
        int id = 0;
        if (stmt != null) {
            boolean flag = true;
            try {
                stmt.executeUpdate(cmdText, Statement.RETURN_GENERATED_KEYS);
                stmt.getGeneratedKeys();
                ResultSet rs = stmt.getGeneratedKeys();
                if(rs.next()) { 
                	id = rs.getInt(1);
                }      
                stmt.cancel();
            } catch (SQLException ex) {
                flag = false;
                log.error(ex);
    			StringWriter errors = new StringWriter();  
    			ex.printStackTrace(new PrintWriter(errors));  
    			log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    ......."); 
            } finally {
                close(stmt, flag);
            }
        }
        return id;
    }

    private static int execSql(String cmdText) {
        log.info(cmdText);
        Statement stmt = getStatement();
        int updateCount = -1;
        if (stmt != null) {
            boolean flag = true;
            try {
                log.info(cmdText);
                updateCount = stmt.executeUpdate(cmdText);
                stmt.cancel();
            } catch (SQLException ex) {
                flag = false;
                log.error(ex);
    			StringWriter errors = new StringWriter();  
    			ex.printStackTrace(new PrintWriter(errors));  
    			log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    ......."); 
            } finally {
                close(stmt, flag);
            }
        }
        return updateCount;
    }

    // [start] 关闭
    private static void close(Statement st, boolean b) {
      try {
        System.out.println("写st关闭正确");
        st.close();
        st.getConnection().close();
      } catch (SQLException e) {
        System.out.println("写st关闭措施");
        throw new RuntimeException(e);
      }
    }

    private static void close(Connection conn, boolean b) {
      try {

        conn.close();
        System.out.println("写conn数据库关闭正确");
      } catch (SQLException e) {
        System.out.println("写conn数据库关闭错误");
        throw new RuntimeException(e);
      }
    }
    public static Object buildScalar(ResultSet rs) {
        if (rs == null) {
            return null;
        }
        Object obj = null;
        try {
            if (rs.next()) {
                obj = rs.getObject(1);
            }
        } catch (SQLException ex) {
            log.error(ex);
			StringWriter errors = new StringWriter();  
			ex.printStackTrace(new PrintWriter(errors));  
			log.error(" exception:"+ex.getMessage()+"\t"+errors.toString()+"    ......."); 
        }
        return obj;
    }

}
